#!/bin/bash

#通过start_time来指定清洗的数据时间范围（昨天：新增/更新）；
#通过end_time来指定获取当前有效的数据。
#清洗掉已删除的数据；
#判断学校id和学科id，把为空的字段统一转换为-1；

SQOOP_HOME=/usr/bin/sqoop

if [ $# -eq 0 ]
then
    DATESTR=`date -d "-1 day" +%Y-%m-%d`
else
    DATESTR=$1
fi

${HIVE_HOME} -e "
--分区
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions.pernode=10000;
SET hive.exec.max.dynamic.partitions=100000;
SET hive.exec.max.created.files=150000;
--hive压缩
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
--写入时压缩生效
SET hive.exec.orc.compression.strategy=COMPRESSION;
--分桶
SET hive.enforce.bucketing=true;
SET hive.enforce.sorting=true;
SET hive.optimize.bucketmapjoin = true;
SET hive.auto.convert.sortmerge.join=true;
SET hive.auto.convert.sortmerge.join.noconditionaltask=true;
--插入数据
INSERT INTO TABLE itcast_dwd.itcast_intention_dwd PARTITION (yearinfo, monthinfo, dayinfo)
SELECT id                                                                                AS rid,
       customer_id                                                                       AS customer_id,
       create_date_time                                                                  AS create_date_time,
       IF((itcast_school_id IS NULL) OR (itcast_school_id = 0), -1, itcast_school_id)    AS itcast_school_id,
       deleted                                                                           AS deleted,
       origin_type                                                                       AS origin_type,
       IF((itcast_subject_id IS NULL) OR (itcast_subject_id = 0), -1, itcast_subject_id) AS itcast_subject_id,
       SUBSTR(create_date_time, 12, 2)                                                   AS hourinfo,
       IF(origin_type = 'NETSERVICE', '1', IF(origin_type = 'PRESIGNUP', '1', '0'))      AS origin_type_stat,
       SUBSTR(create_date_time, 1, 4)                                                    AS yearinfo,
       SUBSTR(create_date_time, 6, 2)                                                    AS monthinfo,
       SUBSTR(create_date_time, 9, 2)                                                    AS dayinfo
FROM itcast_ods.customer_relationship
WHERE deleted = 0
  AND SUBSTR(start_time, 1, 10) = '${DATESTR}'
  AND end_time = '9999-12-31';
"
